* define libraries;
libname hannah  '/projects/users/########';
libname ben '/projects/users/########';
libname hantmp  '/projects/users/########/tempfiles';

* read in /########/CountyLevelWindWrkrs.dta;
proc import out=hannah.countylevelwindwrkrs
  datafile="/projects/users/########/CountyLevelWindWrkrs.dta"
  dbms=dta
  replace;
run;

*define macro "wrkr_cnty" which does X-mile county buffer for all years, states;
%macro wrkr_cnty;

  *get centers of all counties;
  data work.mile20centers;
    merge hannah.USmile20circlesc hannah.centersc;
    by _ID_;
  run;
  data work.mile40centers;
    merge hannah.USmile40circlesc hannah.centersc;
    by _ID_;
  run;
  data work.mile60centers;
    merge hannah.USmile60circlesc hannah.centersc;
    by _ID_;
  run;
  data work.mile80centers;
    merge hannah.USmile80circlesc hannah.centersc;
    by _ID_;
  run;
  data work.mile100centers;
    merge hannah.USmile100circlesc hannah.centersc;
    by _ID_;
  run;

  %do j = 1 %to [number of counties];
    *get individual county buffers; 
    data work.usmile20_&j;
      set hannah.USmile20circlesc (keep = X Y SEGMENT _ID_);
      if _ID_ = &j;
    run;

    data work.usmile40_&j;
      set hannah.USmile40circlesc (keep = X Y SEGMENT _ID_);
      if _ID_ = &j;
    run;

    data work.usmile60_&j;
      set hannah.USmile60circlesc (keep = X Y SEGMENT _ID_);
      if _ID_ = &j;
    run;

    data work.usmile80_&j;
      set hannah.USmile80circlesc (keep = X Y SEGMENT _ID_);
      if _ID_ = &j;
    run;

    data work.usmile100_&j;
      set hannah.USmile100circlesc (keep = X Y SEGMENT _ID_);
      if _ID_ = &j;
    run;

    *get individual county centers;
    data work.uscntycenter_&j;
      set hannah.centersc (keep = _ID_ meanx meany);
      if _ID_ = &j;
    run;
      
    %do yr = 2000 %to 2015;
      *GET counties for each YEAR;
      *Create temp file of all counties near county j in current year;
      proc sql;
	create table hannah.cnties_&j._&yr as
	select 
	  A.*
	from hannah.countylevelwindwrkrs as A
	  left join work.uscntycenter_&j as E on ctyfips
	where A.y between E.meany + 3 and E.meany - 3
	  and A.x between E.meanx + 3 and E.meanx - 3
	  and GEODIST(A.y, A.x, E.meany, E.meanx, 'M' ) <= 103
	  and GEODIST(A.y, A.x, E.meany, E.meanx, 'M' )  ^=.
	  and A.year = &yr
	order by A.ctyfips, A.year;
      quit;
      
      *Flag observations inside X miles, 20, 40, 60, 80, 100;
      PROC GINSIDE 
	DATA= hannah.cnties_&j._&yr
	MAP = work.usmile20_&j
	OUT = hannah.cnties_Xm_&j._&yr;
	ID _ID_;
      RUN;
      data hannah.cnties_Xm_&j._&yr;
	set hannah.cnties_Xm_&j._&yr;
	IF CMISS(_ID_) THEN _ID_ = 0;
	rename _ID_ = id20;
      run;
      PROC GINSIDE 
	DATA= hannah.cnties_Xm_&j._&yr
	MAP = work.usmile40_&j
	OUT = hannah.cnties_Xm_&j._&yr;
	ID _ID_;
      RUN;
      data hannah.cnties_Xm_&j._&yr;
	set hannah.cnties_Xm_&j._&yr;
	IF CMISS(_ID_) THEN _ID_ = 0;
	rename _ID_ = id40;
      run;
      PROC GINSIDE 
	DATA= hannah.cnties_Xm_&j._&yr
	MAP = work.usmile60_&j
	OUT = hannah.cnties_Xm_&j._&yr;
	ID _ID_;
      RUN;
      data hannah.cnties_Xm_&j._&yr;
	set hannah.cnties_Xm_&j._&yr;
	IF CMISS(_ID_) THEN _ID_ = 0;
	rename _ID_ = id60;
      run;
      PROC GINSIDE 
	DATA= hannah.cnties_Xm_&j._&yr
	MAP = work.usmile80_&j
	OUT = hannah.cnties_Xm_&j._&yr;
	ID _ID_;
      RUN;
      data hannah.cnties_Xm_&j._&yr;
	set hannah.cnties_Xm_&j._&yr;
	IF CMISS(_ID_) THEN _ID_ = 0;
	rename _ID_ = id80;
      run;
      PROC GINSIDE 
	DATA= hannah.cnties_Xm_&j._&yr
	MAP = work.usmile100_&j
	OUT = hannah.cnties_Xm_&j._&yr;
	ID _ID_;
      RUN;
      * keep id100 named _ID_;

      * Drop observations outside the buffer;
      DATA hannah.cnties_Xm_&j._&yr;
	SET hannah.cnties_Xm_&j._&yr;
	IF CMISS(_ID_) THEN DELETE;
	plntid = &j;
      RUN;

      data hannah.cnties_Xm_&j._&yr;
	merge hannah.cnties_Xm_&j._&yr (IN = thisj) hannah.centersc;
	by _ID_;
	if thisj = 1;
	drop _TYPE_ _FREQ_;
      run;
      * Collapse to the county level; 
      proc sql;
	CREATE TABLE work.cnties_cntysum_&j._&yr AS
	SELECT _ID_, year,
	SUM(c_tcap) as c_tcap100,
	SUM(total_earn) as total_wages100,
	SUM(total_emp) as total_emp100,
	SUM(tot_white_earn) as tot_white_earn100,
	SUM(total_white_emp) as tot_white_emp100,
	SUM(tot_nonwhite_earn) as tot_nonwhite_earn100,
	SUM(total_nonwhite_emp) as tot_nonwhite_emp100,
	SUM(case when id80~=0 then c_tcap else 0 end) as c_tcap80,
	SUM(case when id80~=0 then total_earn else 0 end) as total_wages80,
	SUM(case when id80~=0 then total_emp else 0 end) as total_emp80,
	SUM(case when id80~=0 then tot_white_earn else 0 end) as tot_white_earn80,
	SUM(case when id80~=0 then total_white_emp else 0 end) as tot_white_emp80,
	SUM(case when id80~=0 then tot_nonwhite_earn else 0 end) as tot_nonwhite_earn80,
	SUM(case when id80~=0 then total_nonwhite_emp else 0 end) as tot_nonwhite_emp80,
	SUM(case when id60~=0 then c_tcap else 0 end) as c_tcap60,
	SUM(case when id60~=0 then total_earn else 0 end) as total_wages60,
	SUM(case when id60~=0 then total_emp else 0 end) as total_emp60,
	SUM(case when id60~=0 then tot_white_earn else 0 end) as tot_white_earn60,
	SUM(case when id60~=0 then total_white_emp else 0 end) as tot_white_emp60,
	SUM(case when id60~=0 then tot_nonwhite_earn else 0 end) as tot_nonwhite_earn60,
	SUM(case when id60~=0 then total_nonwhite_emp else 0 end) as tot_nonwhite_emp60,
	SUM(case when id40~=0 then c_tcap else 0 end) as c_tcap40,
	SUM(case when id40~=0 then total_earn else 0 end) as total_wages40,
	SUM(case when id40~=0 then total_emp else 0 end) as total_emp40,
	SUM(case when id40~=0 then tot_white_earn else 0 end) as tot_white_earn40,
	SUM(case when id40~=0 then total_white_emp else 0 end) as tot_white_emp40,
	SUM(case when id40~=0 then tot_nonwhite_earn else 0 end) as tot_nonwhite_earn40,
	SUM(case when id40~=0 then total_nonwhite_emp else 0 end) as tot_nonwhite_emp40,
	SUM(case when id20~=0 then c_tcap else 0 end) as c_tcap20,
	SUM(case when id20~=0 then total_earn else 0 end) as total_wages20,
	SUM(case when id20~=0 then total_emp else 0 end) as total_emp20,
	SUM(case when id20~=0 then tot_white_earn else 0 end) as tot_white_earn20,
	SUM(case when id20~=0 then total_white_emp else 0 end) as tot_white_emp20,
	SUM(case when id20~=0 then tot_nonwhite_earn else 0 end) as tot_nonwhite_earn20,
	SUM(case when id20~=0 then total_nonwhite_emp else 0 end) as tot_nonwhite_emp20,
	MAX(ctyfips) as ctyfips,
	MAX(meanx) as meanx,
	MAX(meany) as meany
	FROM hannah.cnties_Xm_&j._&yr
	GROUP BY _ID_, year
	ORDER BY _ID_, year;
      quit;
      *get individual county fips code; 
      data work.usmileXvars_&j;
	set work.mile20centers;
	if _ID_ = &j;
      run;
      proc sql;
	create table work.cnty_&j as
	select _ID_,
	MAX(ctyfips) as ctyfips
	FROM work.usmileXvars_&j
	GROUP BY _ID_
	ORDER BY _ID_;
      quit;
      data work.cnties_cntysum_&j._&yr;
	merge work.cnties_cntysum_&j._&yr work.cnty_&j;
	by _ID_;
      run;
	
      PROC APPEND base = hannah.all_cntiesXmile_wrkrs
	data = work.cnties_cntysum_&j._&yr;
      RUN;
      PROC DATASETS library = work nolist;
	delete cnties_cntysum_&j._&yr cnty_&j usmileXvars_&j;
      RUN; QUIT;
      PROC DATASETS library = hannah nolist;
	delete cnties_Xm_&j._&yr cnties_&j._&yr;
      RUN; QUIT;
    %end;
    PROC DATASETS library = work nolist;
      delete usplantcenter_&j usmile20_&j usmile40_&j usmile60_&j usmile80_&j usmile100_&j;
    RUN; QUIT;
  %end;
  PROC DATASETS library = work nolist;
    delete mile20centers mile40centers mile60centers mile80centers mile100centers;
  RUN; QUIT;
%mend;

/*
proc means noprint data=hannah.USmile100circlesc;
  var X Y;
  by _ID_;
  output out = hannah.centersc mean(X Y) = meanx meany;
run;
*/

PROC DATASETS library = hannah nolist;
  delete all_cntiesXmile_wrkrs;
RUN; QUIT;

%wrkr_cnty;

proc sort data=hannah.all_cntiesxmile_wrkrs out=hannah.all_cntiesxmile_wrkrs;
by ctyfips year;
run;
proc sort data=hannah.countylevelwindwrkrs out=hannah.countylevelwindwrkrs;
by ctyfips year;
run;

data hannah.all_cntiesXmile_windwrkrs;
merge hannah.all_cntiesxmile_wrkrs (in=a) hannah.countylevelwindwrkrs (in=b);
by ctyfips year;
run;



